﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using CrystalDecisions.CrystalReports.Engine;

namespace SchoolManagementSystem
{
    public partial class AbsensiReport : Form
    {
        public AbsensiReport()
        {
            InitializeComponent();
        }

        private void num1_ValueChanged(object sender, EventArgs e)
        {
            
        }

        private void num2_ValueChanged(object sender, EventArgs e)
        {
            
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Cursor = Cursors.WaitCursor;

            DataTable data = new DataTable();
            data.Columns.Add("Tanggal");

            int month = cmbMonth.SelectedIndex + 1;
            
            string query = "SELECT * FROM [Group]";
            DataTable dt = Database.getInstance().query(query);

            DataTable[] table = new DataTable[dt.Rows.Count];

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string command = "SELECT abb_date AS Tanggal, COUNT(abb_sick) AS Sakit, COUNT(abb_perm) AS Izin, COUNT(abb_alpha) AS Alpha FROM Student AS s INNER JOIN Enrollment AS e ON s.stu_id = e.stu_id INNER JOIN [Group] AS g ON e.group_id = g.group_id INNER JOIN Absentee AS a ON s.stu_id = a.stu_id WHERE g.group_id = " +  dt.Rows[i].ItemArray[0].ToString() + " AND MONTH(abb_date) = " + month + " AND YEAR(abb_date) = " + num1.Value.ToString() + " GROUP BY abb_date";
                table[i] = Database.getInstance().query(command);
            }
            
            data.Columns.Add("S_KB_K");
            data.Columns.Add("I_KB_K");
            data.Columns.Add("A_KB_K");

            data.Columns.Add("S_KB_B");
            data.Columns.Add("I_KB_B");
            data.Columns.Add("A_KB_B");

            data.Columns.Add("S_TK_B");
            data.Columns.Add("I_TK_B");
            data.Columns.Add("A_TK_B");

            data.Columns.Add("S_TK_A");
            data.Columns.Add("I_TK_A");
            data.Columns.Add("A_TK_A");

            data.Columns.Add("jml_s");
            data.Columns.Add("jml_i");
            data.Columns.Add("jml_a");

            data.Columns.Add("jml_s_total");
            data.Columns.Add("jml_i_total");
            data.Columns.Add("jml_a_total");

            int jml_s_total = 0;
            int jml_i_total = 0;
            int jml_a_total = 0;
            int day = System.DateTime.DaysInMonth((int)num1.Value, month);
            for (int j = 1; j <= day; j++)
            {
                int jml_s = 0;
                int jml_i = 0;
                int jml_a = 0;
                DataRow dataRow = data.NewRow();

                dataRow["Tanggal"] = j;

                foreach(DataRow row in table[0].Rows)
                {
                    DateTime date = (DateTime)row.ItemArray[0];
                    if (date.Day == j)
                    {
                        if ((int)row.ItemArray[1] != 0)
                        {
                            dataRow["S_KB_K"] = row.ItemArray[1].ToString();
                            jml_s = jml_s + (int)row.ItemArray[1];
                        }

                        if ((int)row.ItemArray[2] != 0)
                        {
                            dataRow["I_KB_K"] = row.ItemArray[2].ToString();
                            jml_i = jml_i + (int)row.ItemArray[2];
                        }

                        if ((int)row.ItemArray[3] != 0)
                        {
                            dataRow["A_KB_K"] = row.ItemArray[3].ToString();
                            jml_a = jml_a + (int)row.ItemArray[3];
                        }
                        break;
                    }
                }

                foreach (DataRow row in table[1].Rows)
                {
                    DateTime date = (DateTime)row.ItemArray[0];
                    if (date.Day == j)
                    {
                        if ((int)row.ItemArray[1] != 0)
                        {
                            dataRow["S_KB_B"] = row.ItemArray[1].ToString();
                            jml_s = jml_s + (int)row.ItemArray[1];
                        }

                        if ((int)row.ItemArray[2] != 0)
                        {
                            dataRow["I_KB_B"] = row.ItemArray[2].ToString();
                            jml_i = jml_i + (int)row.ItemArray[2];
                        }

                        if ((int)row.ItemArray[3] != 0)
                        {
                            dataRow["A_KB_B"] = row.ItemArray[3].ToString();
                            jml_a = jml_a + (int)row.ItemArray[3];
                        }
                        break;
                    }
                }

                foreach (DataRow row in table[2].Rows)
                {
                    DateTime date = (DateTime)row.ItemArray[0];
                    if (date.Day == j)
                    {
                        if ((int)row.ItemArray[1] != 0)
                        {
                            dataRow["S_TK_B"] = row.ItemArray[1].ToString();
                            jml_s = jml_s + (int)row.ItemArray[1];
                        }

                        if ((int)row.ItemArray[2] != 0)
                        {
                            dataRow["I_TK_B"] = row.ItemArray[2].ToString();
                            jml_i = jml_i + (int)row.ItemArray[2];
                        }

                        if ((int)row.ItemArray[3] != 0)
                        {
                            dataRow["A_TK_B"] = row.ItemArray[3].ToString();
                            jml_a = jml_a + (int)row.ItemArray[3];
                        }
                        break;
                    }
                }

                foreach (DataRow row in table[3].Rows)
                {
                    DateTime date = (DateTime)row.ItemArray[0];
                    if (date.Day == j)
                    {
                        if ((int)row.ItemArray[1] != 0)
                        {
                            dataRow["S_TK_A"] = row.ItemArray[1].ToString();
                            jml_s = jml_s + (int)row.ItemArray[1];
                        }

                        if ((int)row.ItemArray[2] != 0)
                        {
                            dataRow["I_TK_A"] = row.ItemArray[2].ToString();
                            jml_i = jml_i + (int)row.ItemArray[2];
                        }

                        if ((int)row.ItemArray[3] != 0)
                        {
                            dataRow["A_TK_A"] = row.ItemArray[3].ToString();
                            jml_a = jml_a + (int)row.ItemArray[3];
                        }
                        break;
                    }
                }

                if (jml_s != 0)
                {
                    dataRow["jml_s"] = jml_s;
                    jml_s_total = jml_s_total + jml_s;
                }

                if (jml_s != 0)
                {
                    dataRow["jml_i"] = jml_i;
                    jml_i_total = jml_i_total + jml_i;
                }

                if (jml_s != 0)
                {
                    dataRow["jml_a"] = jml_a;
                    jml_a_total = jml_a_total + jml_a;
                }
              
                data.Rows.Add(dataRow);
            }

            DataTable data2 = new DataTable();
            data2.Columns.Add("jml_s_total");
            data2.Columns.Add("jml_i_total");
            data2.Columns.Add("jml_a_total");
            data2.Columns.Add("month_report");

            DataRow dataRow2 = data2.NewRow();
            dataRow2["jml_s_total"] = jml_s_total;
            dataRow2["jml_i_total"] = jml_i_total;
            dataRow2["jml_a_total"] = jml_a_total;
            dataRow2["month_report"] = cmbMonth.SelectedItem + " " + num1.Value.ToString(); 
            data2.Rows.Add(dataRow2);
            
            /* 
            // Load group
            foreach (DataRow row in dt.Rows)
            {
                string command = "SELECT abb_date AS Tanggal, COUNT(abb_sick) AS Sakit, COUNT(abb_perm) AS Izin, COUNT(abb_alpha) AS Alpha FROM Student AS s INNER JOIN Enrollment AS e ON s.stu_id = e.stu_id INNER JOIN [Group] AS g ON e.group_id = g.group_id INNER JOIN Absentee AS a ON s.stu_id = a.stu_id WHERE g.group_id = " + row.ItemArray[0].ToString() + " AND MONTH(abb_date) = " + month + " AND YEAR(abb_date) = " + num1.Value.ToString() + " GROUP BY abb_date";
                
                // string command = "SELECT abb_date AS Tanggal_" + row.ItemArray[0].ToString() + ", COUNT(abb_sick) AS Sakit_" + row.ItemArray[0].ToString() + ", COUNT(abb_perm) AS Izin_" + row.ItemArray[0].ToString() + ", COUNT(abb_alpha) AS Alpha_" + row.ItemArray[0].ToString() + " FROM Student AS s INNER JOIN Enrollment AS e ON s.stu_id = e.stu_id INNER JOIN [Group] AS g ON e.group_id = g.group_id INNER JOIN Absentee AS a ON s.stu_id = a.stu_id WHERE g.group_id = " + row.ItemArray[0].ToString() + " AND MONTH(abb_date) = " + month + " AND YEAR(abb_date) = " + num1.Value.ToString() + " GROUP BY abb_date";
                
                
                DataTable dTable = Database.getInstance().query(command);

                int count = dTable.Rows.Count;
                string name = row.ItemArray[1].ToString();

                foreach (DataColumn column2 in dTable.Columns)
                {
                    data.Columns.Add(column2.ColumnName);
                }
            }
             * */ 

            /* 
            BindingSource bindingSource1 = new BindingSource();

            this.dataGridView1.Columns.Clear();
            bindingSource1.DataSource = data;
            this.dataGridView1.DataSource = bindingSource1;
             * */

            ReportDocument rptDoc = new ReportDocument();
            alwardahDataSet ds = new alwardahDataSet();

            ds.Tables["AbsenteeReport"].Merge(data);
            ds.Tables["AbsenteeReportNote"].Merge(data2);

            string appPath = (System.IO.Path.GetDirectoryName(Application.ExecutablePath));
            rptDoc.Load(appPath + "\\Report\\ReportAD10.rpt");
            rptDoc.SetDataSource(ds);

            // siapkan report viewer
            ReportForm reportForm = new ReportForm(rptDoc);
            reportForm.ShowDialog();

            Cursor = Cursors.Default;
        }

        private void AbsensiReport_Load(object sender, EventArgs e)
        {          
            DateTime date = DateTime.Now;
            num1.Value = date.Year;
        }
    }
}
